<?php if (! defined ( 'BASEPATH' )) exit ( 'No direct script access allowed' );

class F_flight extends MY_Model {
	
    function __construct() {
    	$table_name = strtolower(get_called_class()); // you can refer to $table_name as specific name
    	$table_fields_info = array(); // you can add table fields into this array()
    	
        parent::__construct($table_name, $table_fields_info);
    }
    
    function get_flights_by_booking_data($booking_data, $direction = 'departure') {
    	if ($direction == 'departure') {
    		$departure_date = $booking_data['departure_date'];
    		$from_airport_id = $booking_data['from_airport'];
    		$to_airport_id = $booking_data['to_airport'];
    	} elseif ($direction == 'return') {
    		$departure_date = $booking_data['return_date'];
    		$from_airport_id = $booking_data['to_airport'];
    		$to_airport_id = $booking_data['from_airport'];
    	} else {
    		return FALSE;
    	}
    	
    	$sql_str =
    	"SELECT
    	
		f_flight_scheduler.flight_code, f_flight_scheduler.from_airport_id, f_flight_scheduler.to_airport_id, f_flight_scheduler.departure_time, f_flight_scheduler.arrival_time,
		f_flight.id AS f_flight_id, f_flight.departure_date, f_flight.economy_class_remain_seats, f_flight.prestige_class_remain_seats,
		const_airplane_model.model_code,
		f_flight_scheduler_price.economy_jeju_discount_price, f_flight_scheduler_price.economy_special_price, f_flight_scheduler_price.economy_basic_price, f_flight_scheduler_price.economy_season_price, f_flight_scheduler_price.prestige_special_price, f_flight_scheduler_price.prestige_basic_price, f_flight_scheduler_price.prestige_season_price
		
    	FROM `f_flight_scheduler`, `f_flight`, `const_airplane_model`, `f_flight_scheduler_price`
		WHERE
		f_flight.flight_scheduler_id = f_flight_scheduler.id
		AND f_flight_scheduler.airplane_model_id = const_airplane_model.id
		AND f_flight_scheduler.from_airport_id = {$from_airport_id}
		AND f_flight_scheduler.to_airport_id = {$to_airport_id}
		AND f_flight_scheduler.flag = 0 AND f_flight.flag = 0 AND const_airplane_model.flag = 0 AND f_flight_scheduler_price.flag = 0
		
		AND (
			(f_flight_scheduler.from_airport_id = f_flight_scheduler_price.from_airport_id AND f_flight_scheduler.to_airport_id = f_flight_scheduler_price.to_airport_id)
			OR
			(f_flight_scheduler.from_airport_id = f_flight_scheduler_price.to_airport_id AND f_flight_scheduler.to_airport_id = f_flight_scheduler_price.from_airport_id)
		)
		
		AND f_flight_scheduler.scheduler_date_id = f_flight_scheduler_price.scheduler_date_id
		AND CONCAT_WS(' ', f_flight.departure_date, f_flight_scheduler.departure_time) > NOW()
		AND f_flight.departure_date = '{$departure_date}'
		AND f_flight_scheduler.scheduler_date_id = (
		SELECT f_flight_scheduler_date.id
		FROM `f_flight_scheduler_date`
		WHERE
		f_flight_scheduler_date.start_date <= f_flight.departure_date <= f_flight_scheduler_date.end_date
		)";
    	
    	return $this->db->query($sql_str)->result_array();
    }
    
    function get_flight_data_by_id($flight_id) {
    	$sql_str =
    	"SELECT

		f_flight_scheduler.flight_code, f_flight_scheduler.from_airport_id, f_flight_scheduler.to_airport_id, f_flight_scheduler.departure_time, f_flight_scheduler.arrival_time,
		f_flight.id AS f_flight_id, f_flight.departure_date, f_flight.economy_class_remain_seats, f_flight.prestige_class_remain_seats,
		const_airplane_model.model_code,
		f_flight_scheduler_price.economy_jeju_discount_price, f_flight_scheduler_price.economy_special_price, f_flight_scheduler_price.economy_basic_price, f_flight_scheduler_price.economy_season_price, f_flight_scheduler_price.prestige_special_price, f_flight_scheduler_price.prestige_basic_price, f_flight_scheduler_price.prestige_season_price
		
		FROM `f_flight_scheduler`, `f_flight`, `const_airplane_model`, `f_flight_scheduler_price`
		WHERE
		f_flight.flight_scheduler_id = f_flight_scheduler.id
		AND f_flight_scheduler.airplane_model_id = const_airplane_model.id
		AND f_flight.id = {$flight_id}
		AND f_flight_scheduler.flag = 0 AND f_flight.flag = 0 AND const_airplane_model.flag = 0 AND f_flight_scheduler_price.flag = 0
		
		AND (
			(f_flight_scheduler.from_airport_id = f_flight_scheduler_price.from_airport_id AND f_flight_scheduler.to_airport_id = f_flight_scheduler_price.to_airport_id)
			OR
			(f_flight_scheduler.from_airport_id = f_flight_scheduler_price.to_airport_id AND f_flight_scheduler.to_airport_id = f_flight_scheduler_price.from_airport_id)
		)
		
		AND f_flight_scheduler.scheduler_date_id = f_flight_scheduler_price.scheduler_date_id
		";
    	
    	return $this->db->query($sql_str)->row_array();
    }
    
    function generate_flights_by_scheduler_id($scheduler_id) {
    	$sql_str = "call generate_flights_by_scheduler_id({$scheduler_id})";
    	$this->db->query($sql_str);
    	return 1;
    }
}

/* This file was generated by class: Tool, function: generate_model */
/* End of file f_flight.php */
/* Location: ./application/models/f_flight.php */
